﻿package com.ld.hpfs.web.controller;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Random;

import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import com.ld.hpfs.common.jackson.JackJson;
import com.ld.hpfs.common.utils.Tools;
import com.ld.hpfs.dao.UtilMapper;
import com.ld.hpfs.pojo.User;
import com.ld.hpfs.service.DealService;
import com.ld.hpfs.service.MdataService;
import com.ld.hpfs.service.RuleService;
import com.ld.hpfs.service.TcodeService;
import com.ld.hpfs.service.UserService;
import com.ld.hpfs.web.interseptor.WebConstants;

@Controller
@RequestMapping("/io")
public class ImportExportController {

	private static final Logger logger = LoggerFactory
			.getLogger(ImportExportController.class);
	@Autowired
	private DealService dealService;

	@Autowired
	private MdataService mdataService;

	@Autowired
	private UtilMapper utilMapper;

	@Autowired
	private UserService userService;

	@Autowired
	private TcodeService tcodeService;

	@Autowired
	private RuleService ruleService;

	// ■规则一览页面：导入按钮
    @RequestMapping(value = "/SetRuleMapping/upload", method = RequestMethod.POST)
    @ResponseBody
    public void SetRuleupload(@RequestParam(value = "upload_file", required = true) MultipartFile multipartFile,
    	 HttpSession session, HttpServletResponse httpServletResponse, HttpServletRequest request, PrintWriter writer) {
        // 获取导入文件名
        String fileName = multipartFile.getOriginalFilename();
        // 获取导入文件类型
        String fileType = FilenameUtils.getExtension(fileName).toLowerCase();
        // 返回信息结果
        HashMap<String,Object> result = new HashMap<String,Object>();
        // 当前登录公司
        String company_name = (String)request.getParameter("company_name") == null ? null : (String)request.getParameter("company_name");
        // 当前用户名
        String user_name = (String)request.getParameter("user_name") == null ? null : (String)request.getParameter("user_name");
        // 工作薄对象
        Workbook wb = null;
        try {
        	// 规则中文文件名
        	String []fileName03Arr={"11_退货.xls",      "12_折扣.xls",       "13_返利.xls",    "14_拆分.xls",     "15_发票分组.xls",
        			                "16_合并明细.xls",  "21_替换商品名.xls", "22_替换客户.xls", "23_替换单位.xls", "24_替换规格.xls",
        			                "31_自定义摘要.xls","32_自定义备注.xls", "33_单据状态.xls"
        	                     };
        	String []fileName07Arr={"11_退货.xlsx",      "12_折扣.xlsx",       "13_返利.xlsx",    "14_Split.xlsx",     "15_Invoice group.xlsx",
		                            "16_合并明细.xlsx",  "21_替换商品名.xlsx", "22_替换客户.xlsx", "23_替换单位.xlsx", "24_替换规格.xlsx",
		                            "31_自定义摘要.xlsx","32_自定义备注.xlsx", "33_单据状态.xlsx"
                                    };
        	// Rule English file name
        	String []fileName03Arr_en={"11_Returns.xls",      "12_Discount.xls",       "13_Rebate.xls",    "14_Split.xls",     "15_Invoice group.xls",
	                				   "16_Contract detail.xls",  "21_Replace goods name.xls", "22_Replace customer.xls", "23_Replace unit.xls", "24_Replace Specifications.xls",
	                				   "31_Customize abstract.xls","32_Customize remark.xls", "33_Document status.xls"  			
        							   };
        	String []fileName07Arr_en={"11_Returns.xls",      "12_Discount.xls",       "13_Rebate.xls",    "14_Split.xls",     "15_Invoice group.xls",
 				   					   "16_Contract detail.xls",  "21_Replace goods name.xls", "22_Replace customer.xls", "23_Replace unit.xls", "24_Replace Specifications.xls",
 				   					   "31_Customize abstract.xls","32_Customize remark.xls", "33_Document status.xls"  			
                    				   };	
        	// 文件名是否正确(check file name rigth or wrong) 
        	boolean fileNameFlag = false;
            for(int  i = 0; i < fileName03Arr.length; i++ ){
            	if(fileName.equals(fileName03Arr[i]) || fileName.equals(fileName07Arr[i])|| fileName.equals(fileName03Arr_en[i]) || fileName.equals(fileName07Arr_en[i])){
            		fileNameFlag = true;
            	    break;
            	}
            }
            
            if(fileNameFlag){
	        	if (!(fileType.equals("xls") || fileType.equals("xlsx") )) {
	        		// 如果不是Excel2003、Excel2007及以上版本文件则导入文件格式不正确
	        		result.put("resultcode", 99);
	            } else {
	            	if(fileType.equals("xls")){
	            		// Excel2003
	            		wb = new HSSFWorkbook(multipartFile.getInputStream());
	            	}else if(fileType.equals("xlsx")){
	            		// Excel2007~Excel2010
	            		wb = new XSSFWorkbook(multipartFile.getInputStream());
	            	}	
	                    String fileName1=(String) fileName.substring(0,2);
	                    // sheet1
	                    Sheet sheet;              
	                    try{
	                    	sheet = wb.getSheetAt(0);
	                    }catch(Exception e){
	                    	logger.error("{}", e);
	                    	result.put("resultcode", 6);
	                    	return;
	            		}
	                    if(sheet.getLastRowNum() < 2 ){
		                	result.put("resultcode", 9);
	                    	return;
		                }
	            		// sheet2
	    	            Sheet sheet2;
	    	            try{
	                    	sheet2 = wb.getSheetAt(1);
	                    }catch(Exception e){
	                    	logger.error("{}", e);
	                    	result.put("resultcode", 7);
	                    	return;
	            		}
	    	            logger.debug("sheet name = " + wb.getSheetName(0));
	    	            logger.debug("sheet name = " + wb.getSheetName(1));
	    	            // sheet1的数据存放与传值参数
	    	            List<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
	    	            HashMap<String,String> map = new HashMap<String,String>();
	    	            HashMap<String, Object> param = new HashMap<String, Object>();
	                    // sheet2的数据存放与传值参数
	    	            List<HashMap<String,String>> list2 = new ArrayList<HashMap<String,String>>();
	    	            HashMap<String,String> map2 = new HashMap<String,String>();
	   	                HashMap<String, Object> param2 = new HashMap<String, Object>();
	    	            // 当前登录用户名
	    	            param.put("account", user_name);
	    	            // 当前登录公司名
	    				param.put("company", company_name);
	    				// 根据用户名、公司名获取当前登录用户ID和公司ID
	    				HashMap<String,Object> check = userService.getUserId(param);
	    				param.put("user_id", check.get("USER_HID"));   	              				
	    				HashMap<String, Object> company = userService.getCompanyId(param); 				
	                    String user_id = check.get("USER_HID").toString();
	                    String company_id = company.get("id").toString(); 
	                    param.put("company_id", company_id);
	                    HashMap<String, Object> company_code = ruleService.getCompanyCode(param); 
	                    String companyCode = (String) company_code.get("COMPANY_CODE");
	                    String ruleLinkID = null;
	    				// 清除数据
	    				param.clear();
	    				// sheet关联ID
	    				String ruleId = "";
	    				
	    			    String []nameList = {  // 规则主表信息
	    			    		"rule_id",     // 规则ID                           
	    			    		"company_id",  // 公司代码
	  	            	        "cust_code",   // 客户代码
	  	            	        "scope_flag",  // 有效范围
	  	            	        "remark",      // 备注	            	 	            	                        
	                          };
//	  	                String titles[] = {"规则ID", "公司代码", "客户代码", "有效范围", "备注" }; 
	  	              
	  	                String []nameList2 = {  // 条件表信息
				    		    "rule_id",      // 规则ID                          
				    		    "conditionProject",// 条件项目
		            	        "condition",    // 条件
		            	        "conditionValue"// 值            	 	            	                        
	                    };
//		                String titles2[] = {"规则ID", "条件项目", "条件", "值" };
		                 
		                // Check公司ID的一致性及客户的存在性checkCompanyFlg:公司是否匹配 Flag,checkCustomerFlg:客户是否存在Flag
		                boolean checkCompanyFlg = true;
		                boolean checkCustomerFlg = true;
		                HashMap<String, String> checkMap = new HashMap<String, String>();
		                HashMap<String, Object> checkParam = new HashMap<String, Object>();
		                String scopeFlag = "";
		                // 循环取得导入文件Sheet1中数据做Check检查
		                for (int i = 2; i <= sheet.getLastRowNum(); i++) {
		                	// 行对象 
		                	Row row = sheet.getRow(i);
		  		            Iterator<Cell> cells = row.cellIterator();
		  		            while (cells.hasNext()){
		  		                 Cell cell = (Cell) cells.next();
		  		                 if(cell.getColumnIndex() < nameList.length && nameList[cell.getColumnIndex()].length() > 0){
		  		                 String value = cell.toString();
		  		                 if(nameList[cell.getColumnIndex()].equals("scope_flag")){
		  		                	 String []scp = value.split(":");
		  		                	 scopeFlag = scp[0];
		  		                 }
		  		                 checkMap.put(nameList[cell.getColumnIndex()], value);
		  		                 }
		  		            }
			  		        if(!(fileName1.equals("21") || fileName1.equals("22") || fileName1.equals("23") || fileName1.equals("24") || fileName1.equals("33"))){
			    	          if(!(checkMap.get("scope_flag").equals("2"))){
			    	           	if(wb.getSheetAt(2).getLastRowNum() < 2){
			    	           	   result.put("resultcode", 10);
				                   return;
			    	            }	
			  		          }
			    	        }
		  		            // 检查Sheet1中公司ID是否和当前登录公司ID一致，若存在不一致则Flag置为False
		  		           if(!(checkMap.get("rule_id").equals("")) && checkMap.get("rule_id") != null)
				  		        if(!(checkMap.get("company_id").equals(companyCode))){
			  		                 checkCompanyFlg = false;	
		  		            }
		  		            // 客户存在性Check
		  		            String comId = session.getAttribute(WebConstants.CURRENT_COMPANY).toString();
		  		            int groupId = utilMapper.getGroupIdFromCompanyId(Integer.parseInt(comId));
		  		            checkParam.put("custCode", checkMap.get("cust_code"));
		  		            checkParam.put("groupId", groupId);
		  		            HashMap<String, Object> sqlResult = ruleService.getCustomerName(checkParam);
		  		            if(scopeFlag.equals("0") || scopeFlag.equals("2")){
		  		             if(sqlResult == null){
			  		                 checkCustomerFlg = false;
			  		            }
		  		            }
		  		            if(checkCompanyFlg == false || checkCustomerFlg == false){
		  		                 break;
		  		            }
		                }
		                if(checkCompanyFlg && checkCustomerFlg){
		                   // 主表信息插入
	  		               for (int i = 2; i <= sheet.getLastRowNum(); i++) {
	  		            	    map = new HashMap<String,String>();
	  		                    // 插入数据
	  		                    Row row = sheet.getRow(i);
	  		                    Iterator<Cell> cells = row.cellIterator();
	  		                    while (cells.hasNext()){
	  		                          Cell cell = (Cell) cells.next();
	  		                          if(cell.getColumnIndex() < nameList.length && nameList[cell.getColumnIndex()].length() > 0){
	  		                    	     String value = cell.toString();
	  		                    	     if(nameList[cell.getColumnIndex()].equals("scope_flag")){
	 		                    	         // 获取有效范围代码
	  		                    	    	 String	[]scoFlg=value.split(":");
	 		                    		     map.put(nameList[cell.getColumnIndex()], scoFlg[0]);
	 		                             }
	  		                    	     else
	  		                    	         map.put(nameList[cell.getColumnIndex()], value);
	  		                          }
	  		                    }
	  		                // 当前登录公司ID,若规则ID为空则不用insert
	  		                if(!map.isEmpty()){
		  		                if(!(map.get("rule_id").equals("")) && map.get("rule_id") != null){
			  		                map.put("company_id1", company_id);
			  		                // 当前用户ID
			  		                map.put("update_id", user_id);
			  		                // 导入规则类型
			      				    map.put("ruleType", fileName1);
			  		                list.add(map);
			  		                param.put("list", list);
			  		                // 规则主表信息导入
			  		                int detail_id = ruleService.getMaxDetailId(fileName1);
			  		                param.put("detail_id", detail_id);
			  		                
			  		                HashMap<String, Object> sqlResult = ruleService.importRuleMain(param);
			  		                // 返回主表当前规则自增ruleID
			  		                ruleLinkID = sqlResult.get("ruleTableLink").toString();
			  		                list.clear(); 
		  		                }
		  		                
			  		    	    if(!(map.get("rule_id").equals("")) && map.get("rule_id") != null){
			  		                // 导入规则条件信息插入
			  		                for (int j = 2; j <= sheet2.getLastRowNum(); j++) {
			  		                	map2 = new HashMap<String,String>();
			  		                	// 构造插入数据
			  		                	ruleId = "";
			  		                	Row row2 = sheet2.getRow(j);
			  		                	Iterator<Cell> cells2 = row2.cellIterator();
			  		                	while (cells2.hasNext()){
			  		                		Cell cell2 = (Cell) cells2.next();
			  		                		if(cell2.getColumnIndex() < nameList2.length && nameList2[cell2.getColumnIndex()].length() > 0){
			  		                			String value2 = cell2.toString();
			  		                			// 条件插入
			  		                			if(nameList2[cell2.getColumnIndex()].equals("rule_id")){
			  		                				ruleId = value2;
			  		                			}
			  		                			if(nameList2[cell2.getColumnIndex()].equals("conditionProject")){
			  		                				if(!value2.equals("") &&  value2 != null){
			  		                					String []value1 = value2.split(":");
			  		                					map2.put(nameList2[cell2.getColumnIndex()], value1[0]);
			  		                				}
			  		                			}
			  		                			else if(nameList2[cell2.getColumnIndex()].equals("condition")){
			  		                				if(!value2.equals("") &&  value2 != null){
			  		                					String []cond=value2.split(":");
			  		                					map2.put(nameList2[cell2.getColumnIndex()], cond[0]);
			  		                				}
			  		                			}
			  		                			else{
			  		                				map2.put(nameList2[cell2.getColumnIndex()], value2);
			  		                			}
			  		                		}
			  		                	}
			  		                	if(!map.isEmpty()){
				  		                	if(ruleId.equals(map.get("rule_id")) || (ruleId+".0").equals(map.get("rule_id"))){
				  		                		map2.put("company_id1", company_id);
				  		                		map2.put("update_id", user_id);
				  		                		map2.put("ruleLinkId", ruleLinkID);
				  		                		list2.add(map2);
				  		                		param2.put("list2", list2);  
				  		                		ruleService.importRuleCondition(param2);
				  		                		list2.clear(); 
				  		                	}
			  		                	}
			  		                }
				 		                   
			  		                // 自定义摘要、自定义备注规则信息导入 
			  		                if((fileName1.equals("31") || fileName1.equals("32")) && !(map.get("scope_flag").equals("2"))){
			 		            	// Sheet3对象
			  		                	Sheet sheet3;
			  		                	try{
			 	                    	sheet3 = wb.getSheetAt(2);
			  		                	}catch(Exception e){
			  		                		logger.error("{}", e);
			  		                		result.put("resultcode", 8);
			  		                		return;
			  		                	}
			  		                	logger.debug("sheet name = " + wb.getSheetName(2));
			  		                	// 摘要和备注的区分Flag
			  		                	String remark_flag;
			  		                	if(fileName1.equals("31")){
			  		                		// 摘要
			  		                		remark_flag = "1";
			  		                	}
			  		                	else{
			  		                		// 备注
			  		                		remark_flag = "0";
			  		                	}
			  		                	// 自定义摘要、自定义备注数据存放和参数信息
			  		                	List<HashMap<String,String>> list3 = new ArrayList<HashMap<String,String>>();
			  		                	HashMap<String,String> map3 = new HashMap<String,String>();
			  		                	HashMap<String, Object> param3 = new HashMap<String, Object>();
			  		                	String []nameList3 = { 
			 	    			    		"rule_id",          // 规则ID
			 	    			    		"selfDefineProject",// 自定义项目
			 	  	            	        "selfDefineType",   // 自定义类型
			 	  	            	        "cut_flag",         // 截取前后Flag
			 	  	            	        "cutLength",        // 截取长度
			 	  	            	        "frontAfterText",   // 前后的文本
			 	  	            	        "frontAferFlag",    // 前后文本Flag
			 	  	            	        "fixedValue"        // 固定值
			 	                          };
		//	 		            	String titles3[] = {"规则ID", "自定义项目", "自定义类型", "截取前后Flg", "截取长度", "前后的文本", "前后文本Flg", "固定值"};
			 		            	 
			  		                	for (int k = 2; k <= sheet3.getLastRowNum(); k++) {
			  		                		map3 = new HashMap<String,String>();
			  		                		// 插入数据
			  		                		ruleId = "";
			  		                		Row row3 = sheet3.getRow(k);
			  		                		Iterator<Cell> cells3 = row3.cellIterator();
			  		                		while (cells3.hasNext()){
			  		                			Cell cell3 = (Cell) cells3.next();
			  		                			if(cell3.getColumnIndex() < nameList3.length && nameList3[cell3.getColumnIndex()].length() > 0){
			  		                				String value3 = cell3.toString(); 
			  		                				
			  		                				if(nameList3[cell3.getColumnIndex()].equals("rule_id")){
			  		                					ruleId = value3;
			  		                				}
			  		                				if(nameList3[cell3.getColumnIndex()].equals("selfDefineProject")){
				  		                				 if(value3.equals(":")){
				  		                					map3.put(nameList3[cell3.getColumnIndex()], "");
				  		                				 }else{
				  		                					String []selfProString = value3.split(":");
				  		                					map3.put(nameList3[cell3.getColumnIndex()], selfProString[0]);
				  		                				 }
			  		                				}else if(nameList3[cell3.getColumnIndex()].equals("selfDefineType")){
			  		                					String []selfType = value3.split(":");
			  		                					map3.put(nameList3[cell3.getColumnIndex()], selfType[0]);
			  		                					if(selfType[0].equals("0")){
			  		                						map3.put(nameList3[5], "");
			  		                						map3.put(nameList3[6], "");
			  		                						map3.put(nameList3[7], "");
			  		                					}
			  		                					else if(selfType[0].equals("1")){
			  		                						map3.put(nameList3[3], "");
			  		                						map3.put(nameList3[4], "");
			  		                						map3.put(nameList3[7], "");
			  		                					}
			  		                					else if(selfType[0].equals("2")){
			  		                						map3.put(nameList3[1], "");
			  		                						map3.put(nameList3[3], "");
			  		                						map3.put(nameList3[4], "");
			  		                						map3.put(nameList3[5], "");
			  		                						map3.put(nameList3[6], "");
			  		                					}
			  		                				}else if(nameList3[cell3.getColumnIndex()].equals("cut_flag")){
			  		                					String []cutFlag = value3.split(":");
			  		                					map3.put(nameList3[cell3.getColumnIndex()], cutFlag[0]);
			  		                				}else if(nameList3[cell3.getColumnIndex()].equals("frontAferFlag")){
			  		                					String []frontAfterString = value3.split(":");
			  		                					map3.put(nameList3[cell3.getColumnIndex()], frontAfterString[0]);
			  		                				}else{
			  		                					map3.put(nameList3[cell3.getColumnIndex()], value3);
			  		                				}
			  		                			}
			  		                		}
			  		                		if(!map3.isEmpty()){
				  		                		if(ruleId.equals(map.get("rule_id")) || (ruleId+".0").equals(map.get("rule_id"))){
				  		                			map3.put("company_id1", company_id);
				  		                			map3.put("remark_flg", remark_flag);
				  		                			map3.put("update_id", user_id);
				  		                			map3.put("ruleLinkId", ruleLinkID);
				  		                			list3.add(map3);
				  		                			param3.put("list3", list3);
				  		                			ruleService.importAbstractRemark(param3);
				  		                			list3.clear();
				  		                		} 
			  		                		}
			  		                	}
			  		                }
								
			  		                // 单据状态规则导入
			  		                if(fileName1.equals("33")  && !(map.get("scope_flag").equals("2"))){
			  		                	Sheet sheet3 = wb.getSheetAt(0);
			  		                	logger.debug("sheet name = " + wb.getSheetName(0));		          
			  		                	// 折扣信息
			  		                	List<HashMap<String,String>> list3 = new ArrayList<HashMap<String,String>>();
			  		                	HashMap<String,String> map3 = new HashMap<String,String>();
			  		                	HashMap<String, Object> param3 = new HashMap<String, Object>();
			  		                	String []nameList3 = {  
			  		                			"rule_id",      // 规则ID
			  		                			"company_id",   // 公司代码
			  		                			"cust_code",    // 客户代码
			  		                			"scope_flag",   // 有效范围
			  		                			"remark",       // 备注	  
			  		                			"doc_status",   // 单据状态
			  		                			"lock_flag",	// 是否保持
			  		                	};
		//		  	                String titles3[] = {"规则ID", "公司代码", "客户代码", "有效范围", "备注", "单据状态", "是否保持"};  
					            	 
			  		                	map3= new HashMap<String,String>();
					 		           // 构造插入数据
			  		                	Row row3 = sheet3.getRow(i);
			  		                	Iterator<Cell> cells3 = row3.cellIterator();
			  		                	while (cells3.hasNext()){
			  		                		Cell cell3 = (Cell) cells3.next();
			  		                		if(cell3.getColumnIndex() < nameList3.length && nameList3[cell3.getColumnIndex()].length() > 0){
			  		                			String value3 = cell3.toString(); 
			  		                			if(nameList3[cell3.getColumnIndex()].equals("doc_status") || nameList3[cell3.getColumnIndex()].equals("lock_flag")){
			  		                				if(value3.equals("") || value3 == null){
			  		                					if(nameList3[cell3.getColumnIndex()].equals("doc_status")){
			  		                						map.put("doc_status","0");// 默认未开票状态
			  		                					}
			  		                					else{
			  		                						map.put("lock_flag", "0");// 保持单据未选中
			  		                					}
			  		                				}
			  		                				else{
			  		                					String []docStatus = value3.split(":");//单据状态 保持状态
			  		                					map3.put(nameList3[cell3.getColumnIndex()], docStatus[0]);
			  		                				}
			  		                			}
			  		                			else{
			  		                				map3.put(nameList3[cell3.getColumnIndex()], value3);
			  		                			}
			  		                		}
			  		                	}
			  		                	if(!map3.isEmpty()){
				  		                	map3.put("update_id", user_id);
				  		                	map3.put("ruleLinkId", ruleLinkID);
				  		                	list3.add(map3);
				  		                	param3.put("list3", list3);  
				  		                	ruleService.importRuleDocStatus(param3);
				  		                	list3.clear(); 
			  		                	}
			  		                }
			 		            
			  		                // 替换商品名、替换客户名、替换规格信息导入 
			  		                if((fileName1.equals("21") || fileName1.equals("22") || fileName1.equals("24")) && !(map.get("scope_flag").equals("2"))){
			  		                	Sheet sheet3 = wb.getSheetAt(0);
			  		                	logger.debug("sheet name = " + wb.getSheetName(0));
			  		                	String replaceCol;
			  		                	if(fileName1.equals("21")){
			  		                		replaceCol = "D_GOODS_NAME";
			  		                	}
			  		                	else if(fileName1.equals("22")){
			  		                		replaceCol = "H_CUST_NAME_CHN";
			  		                	}
						            	else{
						            		replaceCol = "D_SPEC";
						            	}
					    	            // 替换商品名、替换客户名、替换规格---sheet3数据存放和参数
					    	            List<HashMap<String,String>> list3 = new ArrayList<HashMap<String,String>>();
					    	            HashMap<String,String> map3 = new HashMap<String,String>();
					    	            HashMap<String, Object> param3 = new HashMap<String, Object>();
					    	            String []nameList3 = {
					    			    		"rule_id",    // 规则ID
					    			    		"company_id", // 公司代码
					  	            	        "cust_code",  // 客户代码
					  	            	        "scope_flag", // 有效范围
					  	            	        "remark",     // 备注	  
					  	            	        "replaceEnd"  // 替换成
					                          };
			//		  	                String titles3[] = {"规则ID", "公司代码", "客户代码", "有效范围", "备注", "替换成"}; 
						 		        
					    	            map3 = new HashMap<String,String>();
						 		        // 构造插入数据
						 		        Row row3 = sheet3.getRow(i);
						 		        Iterator<Cell> cells3 = row3.cellIterator();
						 		        while (cells3.hasNext()){
						 		        	Cell cell3 = (Cell) cells3.next();
						 		        	if(cell3.getColumnIndex() < nameList3.length && nameList3[cell3.getColumnIndex()].length() > 0){
						 		        		String value3 = cell3.toString(); 
						 		        		if(nameList3[cell3.getColumnIndex()].equals("rule_id")){
						 		        			ruleId = value3;
						 		        		}
						 		        		if(nameList3[cell3.getColumnIndex()].equals("scope_flag")){
						 		        			String []scoFlg=value3.split(":");
						 		        			map3.put(nameList[cell3.getColumnIndex()], scoFlg[0]);
						 		        		}
						 		        		else
						 		        			map3.put(nameList3[cell3.getColumnIndex()], value3);
						 		        	}
						 		        }
						 		        if(!map3.isEmpty()){
							 		        if(ruleId.equals(map.get("rule_id")) || (ruleId+".0").equals(map.get("rule_id"))){
								 		        map3.put("company_id1", company_id);
								 		        map3.put("update_id", user_id);
							 				    map3.put("ruleLinkId", ruleLinkID);
							 				    map3.put("replaceColumn", replaceCol);
								 		        list3.add(map3);
								 		        param3.put("list3", list3);
								 		        ruleService.importReplaceGoodsCustType(param3);
								 		    	list3.clear(); 
								 		    }
						 		        }
						            }
						            
						            // 替换单位规则导入
						            if(fileName1.equals("23") && !(map.get("scope_flag").equals("2"))){
						            	Sheet sheet3 = wb.getSheetAt(0);
						            	logger.debug("sheet name = " + wb.getSheetName(0));		          
					    	            // 替换单位数据保存与传值参数
					    	            List<HashMap<String,String>> list3 = new ArrayList<HashMap<String,String>>();
					    	            HashMap<String,String> map3 = new HashMap<String,String>();
					    	            HashMap<String, Object> param3 = new HashMap<String, Object>();
					    	            String []nameList3 = {  // 替换单位表信息
					    			    		"rule_id",      // 规则ID
					    			    		"company_id",   // 公司代码
					  	            	        "cust_code",    // 客户代码
					  	            	        "scope_flag",   // 有效范围
					  	            	        "remark",       // 备注	  
					  	            	        "replaceBeforeUnit",// 替换前单位
					  	            	        "replaceUnitRate",  // 替换率
					  	            	        "replaceAfterUnit"  // 替换后单位
					                          };
			//		  	                String titles3[] = {"规则ID", "公司代码", "客户代码", "有效范围", "备注", "替换前单位", "替换率", "替换后单位"}; 
			
						 		        map3= new HashMap<String,String>();
						 		        // 构造插入数据
						 		        Row row3 = sheet3.getRow(i);
						 		        Iterator<Cell> cells3 = row3.cellIterator();
						 		        while (cells3.hasNext()){
						 		        	Cell cell3 = (Cell) cells3.next();
						 		        	if(cell3.getColumnIndex() < nameList3.length && nameList3[cell3.getColumnIndex()].length() > 0){
						 		        		String value3 = cell3.toString(); 
						 		        		if(nameList3[cell3.getColumnIndex()].equals("scope_flag")){
						 		        			String	[]scoFlg = value3.split(":");
						 		        			map3.put(nameList[cell3.getColumnIndex()], scoFlg[0]);
						 		        		}
						 		        		else
						 		        			map3.put(nameList3[cell3.getColumnIndex()], value3);
						 		        	}
						 		        }
						 		        if(!map3.isEmpty()){
							 		        map3.put("company_id1", company_id);
							 		        map3.put("update_id", user_id);
							 		        map3.put("ruleLinkId", ruleLinkID);
							 		        list3.add(map3);
							 		        param3.put("list3", list3);  
							 		        ruleService.importReplaceUnit(param3);
							 		        list3.clear(); 
						 		        }
						            }
						             
						            		            
						            // 特殊规则共通导入11:退货12:折扣13:返利14:拆分15:发票分组16:合并明细
						            if((fileName1.equals("11") || fileName1.equals("12") || fileName1.equals("13") || fileName1.equals("14") || fileName1.equals("15") || fileName1.equals("16")) && !(map.get("scope_flag").equals("2"))){
						            	// Sheet3
						            	Sheet sheet3;
				 	    	            try{
				 	                    	sheet3 = wb.getSheetAt(2);
				 	                    }catch(Exception e){
				 	                    	logger.error("{}", e);
				 	                    	result.put("resultcode", 8);
				 	                    	return;
				 	            		}
						            	logger.debug("sheet name = " + wb.getSheetName(2));
					    	            // 特殊规则---sheet3
					    	            List<HashMap<String,String>> list3 = new ArrayList<HashMap<String,String>>();
					    	            HashMap<String,String> map3 = new HashMap<String,String>();
					    	            HashMap<String, Object> param3 = new HashMap<String, Object>();
						            	String []nameList3 = {
					    			    		"rule_id",      // 规则ID
					    			    		"adjustProject",// 调整项目
					    			    		"adjustMode",   // 调整方式
					    			    		"fixedValue"    // 固定值
					                          };
			//			            	String titles3[] = {"规则ID", "调整项目", "调整方式", "固定值"};
						            	 
						                for (int  k= 2; k <= sheet3.getLastRowNum(); k++) {
						 		             map3= new HashMap<String,String>();
						 		             // 构造插入数据
						 		             ruleId = "";
						 		             Row row3 = sheet3.getRow(k);
						 		             if(row3 != null){
							 		             Iterator<Cell> cells3 = row3.cellIterator();
							 		             while (cells3.hasNext()){
							 		            	 Cell cell3 = (Cell) cells3.next();
							 		            	 if(cell3.getColumnIndex() < nameList3.length && nameList3[cell3.getColumnIndex()].length() > 0){
							 		            		 String value3 = cell3.toString(); 
							 		            		 if(nameList3[cell3.getColumnIndex()].equals("rule_id")){
								 		            		ruleId = value3;
							 		            		 }
							 		            		 if(nameList3[cell3.getColumnIndex()].equals("adjustProject")){
							 		            			 String []adjProject=value3.split(":");
							 		            			 map3.put(nameList3[cell3.getColumnIndex()], adjProject[0]);
							 		            		 }
							 		            		 else if(nameList3[cell3.getColumnIndex()].equals("adjustMode")){
							 		            			 String []adjMode=value3.split(":");
							 		            			 map3.put(nameList3[cell3.getColumnIndex()], adjMode[0]);
							 		            		 }
							 		            		 else{
							 		            			 map3.put(nameList3[cell3.getColumnIndex()], value3);
							 		            		 }
							 		                 }
							 		             }
							 		             if(!map3.isEmpty()){
								 		             if(ruleId.equals(map.get("rule_id")) || (ruleId+".0").equals(map.get("rule_id"))){
									 		             map3.put("company_id1", company_id);
									 		             map3.put("update_id", user_id);
								 				         map3.put("ruleLinkId", ruleLinkID);
									 		             list3.add(map3);
									 		             param3.put("list3", list3);  
									 		             ruleService.importSpecialRule(param3);
									 		    	     list3.clear();
								 		             }
							 		             }
						 		             }
						                } 
						            }
						            
						             // 折扣信息表导入     
						             if(fileName1.equals("12")  && !(map.get("scope_flag").equals("2"))){
						            	    Sheet sheet3 = wb.getSheetAt(0);
							            	logger.debug("sheet name = " + wb.getSheetName(0));		          
						    	            // 折扣信息
						    	            List<HashMap<String,String>> list3 = new ArrayList<HashMap<String,String>>();
						    	            HashMap<String,String> map3 = new HashMap<String,String>();
						    	            HashMap<String, Object> param3 = new HashMap<String, Object>();
						    	            String []nameList3 = {  
						    			    		"rule_id",      // 规则ID
						    			    		"company_id",   // 公司代码
						  	            	        "cust_code",    // 客户代码
						  	            	        "scope_flag",   // 有效范围
						  	            	        "remark",       // 备注	  
						  	            	        "discountMax"   // 折扣上限
						                          };
			//			  	                String titles3[] = {"规则ID", "公司代码", "客户代码", "有效范围", "备注", "折扣上限"};  
							            	
							 		        map3= new HashMap<String,String>();
							 		        // 构造插入数据
							 		        Row row3 = sheet3.getRow(i);
							 		        Iterator<Cell> cells3 = row3.cellIterator();
							 		        while (cells3.hasNext()){
							 		        	Cell cell3 = (Cell) cells3.next();
							 		        	if(cell3.getColumnIndex() < nameList3.length && nameList3[cell3.getColumnIndex()].length() > 0){
							 		        		String value3 = cell3.toString(); 
							 		        		map3.put(nameList3[cell3.getColumnIndex()], value3);
							 		        	}
							 		        }
							 		        if(!map3.isEmpty()){
								 		        map3.put("company_id1", company_id);
								 		        map3.put("update_id", user_id);
								 		        map3.put("ruleLinkId", ruleLinkID);
								 		        list3.add(map3);
								 		        param3.put("list3", list3);  
								 		        ruleService.importRuleDiscount(param3);
								 		        list3.clear(); 
							 		        }
						             }
						             
						             // 返利信息导入
						             if(fileName1.equals("13")  && !(map.get("scope_flag").equals("2"))){
						            	Sheet sheet3 = wb.getSheetAt(0);
							            logger.debug("sheet name = " + wb.getSheetName(0));		          
						    	        //返利
						    	        List<HashMap<String,String>> list3 = new ArrayList<HashMap<String,String>>();
						    	        HashMap<String,String> map3 = new HashMap<String,String>();
						    	        HashMap<String, Object> param3 = new HashMap<String, Object>();
						    	        String []nameList3 = {  // 返利表信息
						    			    		"rule_id",       // 规则ID
						    			    		"company_id",    // 公司代码
						  	            	        "cust_code",     // 客户代码
						  	            	        "scope_flag",    // 有效范围
						  	            	        "remark",        // 备注	  
						  	            	        "rebateTotal",   // 返利总额
						  	            	        "rebatePriceWay",// 计价方式
						  	            	        "rebateBalance", // 返利余额
						  	            	        "timeStart",     // 有效期间from
						  	            	        "timeEnd",       // 有效期间to
						  	            	        "rebateRate"     // 返利率
						                          };
			//			  	            String titles3[] = {"规则ID", "公司代码", "客户代码", "有效范围", "备注", "返利总额", "计价方式", "返利余额", "有效期间from", "有效期间to", "返利率"};  
			
							 		    map3= new HashMap<String,String>();
							 		    // 构造插入数据
							 		    Row row3 = sheet3.getRow(i);
							 		    Iterator<Cell> cells3 = row3.cellIterator();
							 		    while (cells3.hasNext()){
							 		        Cell cell3 = (Cell) cells3.next();
							 		        if(cell3.getColumnIndex() < nameList3.length && nameList3[cell3.getColumnIndex()].length() > 0){
							 		           String value3 = cell3.toString(); 
							 		           if(nameList3[cell3.getColumnIndex()].equals("rebatePriceWay")){
							 		        	   String priceWay[] = value3.split(":");
							 		        	   map3.put(nameList3[cell3.getColumnIndex()], priceWay[0]);
							 		           }else{
							 		        	   map3.put(nameList3[cell3.getColumnIndex()], value3);
							 		           }
							 		        }
							 		    }
							 		    if(!map3.isEmpty()){
								 		    map3.put("company_id1", company_id);
								 		    map3.put("update_id", user_id);
							 				map3.put("ruleLinkId", ruleLinkID);
								 		    list3.add(map3);
								 		    param3.put("list3", list3);  
								 		    ruleService.importRuleRebate(param3);
								 		    list3.clear(); 
							 		    }
						              }
						            			             
						             // 拆分信息导入信息导入
						             if(fileName1.equals("14")  && !(map.get("scope_flag").equals("2"))){
						            	    Sheet sheet3 = wb.getSheetAt(0);
							                logger.debug("sheet name = " + wb.getSheetName(0));		          
						    	            //拆分
						    	            List<HashMap<String,String>> list3 = new ArrayList<HashMap<String,String>>();
						    	            HashMap<String,String> map3 = new HashMap<String,String>();
						    	            HashMap<String, Object> param3 = new HashMap<String, Object>();
						    	            String []nameList3 = { 
						    			    		"rule_id",      // 规则ID
						    			    		"company_id",   // 公司代码
						  	            	        "cust_code",    // 客户代码
						  	            	        "scope_flag",   // 有效范围
						  	            	        "remark",       // 备注	  
						  	            	        "splitMode",    // 拆分方式
						  	            	        "maxAmount",    // 最大金额
						  	            	        "splitRate1",   // 拆分比率1
						  	            	        "splitRate2",   // 拆分比率2
						  	            	        "splitRate3",   // 拆分比率3
						  	            	        "splitRate4",   // 拆分比率4
						  	            	        "splitRate5"    // 拆分比率5
						                          };
			//			  	                String titles3[] = {"规则ID", "公司代码", "客户代码", "有效范围","备注", "拆分方式",
			//			  	                		            "最大金额", "拆分比率1", "拆分比率2", "拆分比率3", "拆分比率4", "拆分比率5"};  
							            	 
							 		        map3= new HashMap<String,String>();
							 		        // 构造插入数据
							 		        Row row3 = sheet3.getRow(i);
							 		        Iterator<Cell> cells3 = row3.cellIterator();
							 		        while (cells3.hasNext()){
							 		        	Cell cell3 = (Cell) cells3.next();
							 		        	if(cell3.getColumnIndex() < nameList3.length && nameList3[cell3.getColumnIndex()].length() > 0){
							 		        		String value3 = cell3.toString(); 
							 		        		if(nameList3[cell3.getColumnIndex()].equals("splitMode")){
							 		        			String []splitMod=value3.split(":");
							 		        			map3.put(nameList3[cell3.getColumnIndex()], splitMod[0]);
							 		        			if(splitMod[0].equals("0")){
							 		                    	map3.put(nameList3[7], "");
							 		                    	map3.put(nameList3[8], "");
							 		                    	map3.put(nameList3[9], "");
							 		                    	map3.put(nameList3[10], "");
							 		                    	map3.put(nameList3[11], ""); 
							 		                    }
							 		                    else{
							 		                    	map3.put(nameList3[6], "");
							 		                    }
							 		        		}
							 		        		else
							 		        			map3.put(nameList3[cell3.getColumnIndex()], value3);
							 		        	}
							 		        }
							 		        if(!map3.isEmpty()){
								 		        map3.put("company_id1", company_id);
								 		        map3.put("update_id", user_id);
								 		        map3.put("ruleLinkId", ruleLinkID);
								 		        list3.add(map3);
								 		        param3.put("list3", list3);  
								 		        ruleService.importRuleSplit(param3);
								 		        list3.clear(); 
							 		        }
						             }
			  		    	    }
	  		                }
	  		               }
	  		               // 导入成功
	  		               result.put("resultcode",0); 
			          }
		  		      else{
		  		         if(checkCompanyFlg == false && checkCustomerFlg == true ){
		  		        	 // 当前登录公司代码与导入文件中代码不一致
		  		        	 result.put("resultcode",2); 
		  		         }
		  		         else if(checkCustomerFlg == false && checkCompanyFlg == true){
		  		        	 // 文件中有公司代码不存在，请核查
		  		        	 result.put("resultcode",3);
		  		         }
		  		         else if(checkCustomerFlg == false && checkCompanyFlg == false){
		  		        	 // 文件中公司ID不正确并且有客户不存在
		  		        	 result.put("resultcode",4);
		  		         }
		  		       }
	               }	
	  		    }
	            if(fileNameFlag == false)
	        		result.put("resultcode",5);	
		            Cookie cookie = new Cookie("JSESSIONID", "");
		            cookie.setMaxAge(0);
		            httpServletResponse.addCookie(cookie);
		        } catch (Exception e) {
		            logger.error("{}", e);
		        } finally {
		        	if(result.get("resultcode") == null){
		        		result.put("resultcode", 11);
		        	}
		        	String returnMsg = JackJson.fromObjectToJson(result);
		        	writer.print(returnMsg);
		            writer.flush();
		            writer.close();
		        }
	     }
    
	// 客户主数据导入
	@RequestMapping(value = "/MdataClientMapping/upload", method = RequestMethod.POST)
	@ResponseBody
	public void MdataClientupload(
			@RequestParam(value = "upload_file", required = true) MultipartFile multipartFile,
			HttpSession session, HttpServletResponse httpServletResponse,
			HttpServletRequest request, PrintWriter writer) {
		// 获得语言环境
		String language = (String) session.getAttribute(WebConstants.CURRENT_LANG);
		String fileName = multipartFile.getOriginalFilename();
		String fileType = FilenameUtils.getExtension(fileName).toLowerCase();
		HashMap<String, Object> result = new HashMap<String, Object>();
		String company_name = (String) request.getParameter("company_name") == null ? null
				: (String) request.getParameter("company_name");
		String user_name = (String) request.getParameter("user_name") == null ? null
				: (String) request.getParameter("user_name");
		Workbook wb = null;
		// String check_custcode = "(";
		try {
			if (!(fileType.equals("xls") || fileType.equals("xlsx"))) {
				result.put("resultcode", 1);
			} else {
				if (fileType.equals("xls")) {// 如果导入的是excel2003
					wb = new HSSFWorkbook(multipartFile.getInputStream());
				} else if (fileType.equals("xlsx")) {// 如果导入的是excel2007
					wb = new XSSFWorkbook(multipartFile.getInputStream());
				}
				Sheet sheet = wb.getSheetAt(0);
				Sheet sheet2;
				try{
					sheet2 = wb.getSheetAt(1);
				}catch(Exception e){
					result.put("resultcode", 99);
					return;
				}
				logger.debug("sheet name = " + wb.getSheetName(0));
				logger.debug("sheet name = " + wb.getSheetName(1));
				List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
				HashMap<String, String> map = new HashMap<String, String>();
				HashMap<String, Object> param = new HashMap<String, Object>();
				List<HashMap<String, String>> list2 = new ArrayList<HashMap<String, String>>();
				HashMap<String, String> map2 = new HashMap<String, String>();
				HashMap<String, Object> param2 = new HashMap<String, Object>();
				param.put("create_time", Tools.getCurrentDay()); // 插入到数据库UPDATE_TIME时，因为UPDATE_TIME字段长度不够，因此字符串被截取
				param.put("account", user_name);
				param.put("company", company_name);

				HashMap<String, Object> check = userService.getUserId(param);
				param.put("user_id", check.get("USER_HID"));

				// 更新update_id,update_name信息 by guxiaoli
				User user = (User) session
						.getAttribute(WebConstants.CURRENT_USER);
				param.put("update_id", Integer.parseInt(user.getUserId()));
				param.put("update_name", user.getAccount());

				HashMap<String, Object> company = userService
						.getCompanyId(param);
				int groupId = utilMapper
						.getGroupIdFromCompanyId((Integer) company.get("id"));
				param.put("group_id", groupId);
				param2.put("group_id", groupId);
				// 更新update_id,update_name信息 by guxiaoli
				param2.put("update_id", Integer.parseInt(user.getUserId()));
				param2.put("update_name", user.getAccount());
				param2.put("create_time", Tools.getCurrentDay());
                param2.put("user_id", Integer.parseInt(user.getUserId()));

				String[] nameList = { // 基本信息
				"code", "client_supplier", "tax_code", "address",
						"bank", "bank_account", "tel","doc_type", "ck01", "ck02", "ck03",
						"ck04", "ck05", "ck06", "ck07", "ck08", "ck09", "ck10" };
				String titles[] = new String[18];
	    		if(language.equals("en")){
	    			String titles_en[] = { "Customer_code", "Customer_name","Taxpayer's identification No.","Address","Customer_openBank","Customer_bankAccount","Customer_telphone","Document_type",
	    								   "Reference field 1","Reference field 2","Reference field 3","Reference field 4","Reference field 5","Reference field 6","Reference field 7",
	        							   "Reference field 8","Reference field 9","Reference field 10"};   		    
	    		    for(int i = 0; i <titles_en.length; i++){
	    		    	titles[i] =  titles_en[i];
	    		    }
	    		}else if(language.equals("zh_CN")){
	        		String titles_cn[] = { "客户代码", "客户名称","客户纳税人识别号","地址","客户开户行","客户账号","客户电话","发票类型"
	        				,"参考字段1","参考字段2","参考字段3","参考字段4","参考字段5","参考字段6","参考字段7","参考字段8","参考字段9","参考字段10"};
	        		for(int i = 0; i <titles_cn.length; i++){
	    		    	titles[i] =  titles_cn[i];
	    		    }
	    		}
				String[] nameList2 = { // 联系信息
						 "main_name","recv_company", "recv_ticket", "recv_address", "recv_tel",
						 "city", "remark","recv_email","recv_phone", "ck01", "ck02", "ck03", "ck04",
						 "ck05",};
				// 联系信息(Contact information)
				String titles2[] = new String[14];
	    		if(language.equals("en")){
	    			String titles2_en[] = {"Cust_code","Receiver_companyName", "Invoice_receiverName","Receiver_address","Receiver_companyTelphone","City",
	    								   "Remark","Receiver_email","Contact_mobilephone","Reference field 1","Reference field 2","Reference field 3","Reference field 4","Reference field 5" };
	    		    for(int i = 0; i < titles2_en.length; i++){
	    		    	titles2[i] = titles2_en[i];
	    		    }
	    		}else if(language.equals("zh_CN")){
	    			String titles2_cn[] = {"客户代码","收件公司名称", "发票收件人","收件人地址","收件单位电话","城市","备注",
	    					               "收件人邮箱","联系人手机","参考字段1","参考字段2","参考字段3","参考字段4","参考字段5" };
	    			 for(int i = 0; i < titles2_cn.length; i++){
	     		    	titles2[i] = titles2_cn[i];
	    			 }
	    		}
				// 基本信息插入
				Iterator<Cell> titleCells = sheet.getRow(0).cellIterator();
				boolean flag = false;
				while (titleCells.hasNext()) {
					Cell titleCell = (Cell) titleCells.next();
					if (titleCell.getColumnIndex() < nameList.length
							&& nameList[titleCell.getColumnIndex()].length() > 0) {
						String value = titleCell.toString();
						if (!titles[titleCell.getColumnIndex()].equals(value)) {
							flag = true;
							break;
						}
					}
				}
				if (!flag) {
					int sqlResult = 0;
					for (int i = 1; i <= sheet.getLastRowNum(); i++) {
						map = new HashMap<String, String>();
						// 构造插入数据
						Row row = sheet.getRow(i);
						Iterator<Cell> cells = row.cellIterator();
						while (cells.hasNext()) {
							Cell cell = (Cell) cells.next();
							if (cell.getColumnIndex() < nameList.length
									&& nameList[cell.getColumnIndex()].length() > 0) {
								String value = cell.toString();
								if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
									value = String.format("%.0f",
											Double.valueOf(value));
								}
								if(language.equals("en")){
									map.put(nameList[cell.getColumnIndex()], value);
								}else if(language.equals("zh_CN")){
									if(nameList[cell.getColumnIndex()].equals("doc_type")){
										if(value.trim().equals("营业税发票")){
											value = "BT";
										}else if(value.trim().equals("出口发票")){
											value = "E_INV";
										}else if(value.trim().equals("增值税普通发票")){
											value = "N_VAT";
										}else if(value.trim().equals("交通运输发票")){
											value = "T_INV";
										}else if(value.trim().equals("增值税专用发票")){
											value = "VAT";
										}else{
											value = "ImportType";
										}
									}
									map.put(nameList[cell.getColumnIndex()], value);
								}
							}
						}
					

						list.add(map);

						if (list.size() >= 10) {
							param.put("list", list);
							// check_custcode = check_custcode + ")";
							// param.put("check_custcode", check_custcode);
							sqlResult = mdataService
									.insertMdataClientMappingForImport(param);
							list.clear();
							// check_custcode = "(";
							if (sqlResult != 0) {
								break;
							}
						}
					}

					if (list.size() > 0) {
						param.put("list", list);
						// check_custcode = check_custcode + ")";
						// param.put("check_custcode", check_custcode);
						sqlResult = mdataService
								.insertMdataClientMappingForImport(param);
						// check_custcode = "(";
					}

					// User user =
					// (User)request.getSession().getAttribute(WebConstants.CURRENT_USER);
					if (sqlResult == 0) {
						result.put("resultcode", 0);
					} else {
						result.put("resultcode", 9);
					}
				} else {
					result.put("resultcode", 99);
				}
				// 联系信息插入
				Iterator<Cell> titleCells2 = sheet2.getRow(0).cellIterator();
				boolean flag2 = false;
				while (titleCells2.hasNext()) {
					Cell titleCell2 = (Cell) titleCells2.next();
					if (titleCell2.getColumnIndex() < nameList2.length
							&& nameList2[titleCell2.getColumnIndex()].length() > 0) {
						String value2 = titleCell2.toString();
						if (!titles2[titleCell2.getColumnIndex()]
								.equals(value2)) {
							flag2 = true;
							break;
						}
					}
				}
				if (!flag2) {
					int sqlResult2 = 0;
					for (int i = 1; i <= sheet2.getLastRowNum(); i++) {
						map2 = new HashMap<String, String>();
						// 构造插入数据
						Row row2 = sheet2.getRow(i);
						Iterator<Cell> cells2 = row2.cellIterator();
						while (cells2.hasNext()) {
							Cell cell2 = (Cell) cells2.next();
							if (cell2.getColumnIndex() < nameList2.length
									&& nameList2[cell2.getColumnIndex()]
											.length() > 0) {
								String value2 = cell2.toString();
								if (cell2.getCellType() == Cell.CELL_TYPE_NUMERIC) {
									value2 = String.format("%.0f",
											Double.valueOf(value2));
								}
								map2.put(nameList2[cell2.getColumnIndex()],
										value2);
							}
						}
						list2.add(map2);

						if (list2.size() >= 10) {
							param2.put("list", list2);
							sqlResult2 = mdataService
									.insertMdataClientAddressMappingForImport(param2);
							list2.clear();
							if (sqlResult2 != 0) {
								break;
							}
						}
					}

					if (list2.size() > 0) {
						param2.put("list", list2);
						sqlResult2 = mdataService
								.insertMdataClientAddressMappingForImport(param2);
					}

					// User user =
					// (User)request.getSession().getAttribute(WebConstants.CURRENT_USER);
					if (sqlResult2 == 0) {
						result.put("resultcode", 0);
					} else {
						result.put("resultcode", 9);
					}
				} else {
					result.put("resultcode", 99);
				}
			}

			Cookie cookie = new Cookie("JSESSIONID", "");
			cookie.setMaxAge(0);
			httpServletResponse.addCookie(cookie);
		} catch (Exception e) {
			logger.error("{}", e);
		} finally {
			if (result.get("resultcode") == null) {
				result.put("resultcode", 9);
			}
			String returnMsg = JackJson.fromObjectToJson(result);
			writer.print(returnMsg);
			writer.flush();
			writer.close();
		}
	}

	@RequestMapping(value = "/MdataGoodsMapping/upload", method = RequestMethod.POST)
	@ResponseBody
	public void MdataGoodsupload(
			@RequestParam(value = "upload_file", required = true) MultipartFile multipartFile,
			HttpSession session, HttpServletResponse httpServletResponse,
			HttpServletRequest request, PrintWriter writer) {
		// 获得语言环境
		String language = (String) session.getAttribute(WebConstants.CURRENT_LANG);
		String fileName = multipartFile.getOriginalFilename();
		String fileType = FilenameUtils.getExtension(fileName).toLowerCase();
		HashMap<String, Object> result = new HashMap<String, Object>();
		String company_name = (String) request.getParameter("company_name") == null ? null
				: (String) request.getParameter("company_name");
		String user_name = (String) request.getParameter("user_name") == null ? null
				: (String) request.getParameter("user_name");
		Workbook wb = null;
		try {
			if (!(fileType.equals("xls") || fileType.equals("xlsx"))) {
				result.put("resultcode", 1);
			} else {
				if (fileType.equals("xls")) {// 如果导入的是excel2003
					wb = new HSSFWorkbook(multipartFile.getInputStream());
				} else if (fileType.equals("xlsx")) {// 如果导入的是excel2007
					wb = new XSSFWorkbook(multipartFile.getInputStream());
				}
				Sheet sheet = wb.getSheetAt(0);
				logger.debug("sheet name = " + wb.getSheetName(0));
				List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
				HashMap<String, String> map = new HashMap<String, String>();
				HashMap<String, Object> param = new HashMap<String, Object>();
				param.put("create_time", Tools.getCurrentDay()); // 插入到数据库UPDATE_TIME时，因为UPDATE_TIME字段长度不够，因此字符串被截取
				param.put("account", user_name);
				param.put("company", company_name);

				HashMap<String, Object> check = userService.getUserId(param);
				param.put("user_id", check.get("USER_HID"));

				HashMap<String, Object> company = userService
						.getCompanyId(param);
				int groupId = utilMapper
						.getGroupIdFromCompanyId((Integer) company.get("id"));
				param.put("group_id", groupId);

				// 更新update_id,update_name信息 by guxiaoli
				User user = (User) session
						.getAttribute(WebConstants.CURRENT_USER);
				param.put("update_id", Integer.parseInt(user.getUserId()));
				param.put("update_name", user.getAccount());

				String[] nameList = { "code", "name", "tax_item", "tax_rate",
						"specification", "measurement", "default_price",
						"shortcode" };
				String titles[] = new String[8];
		        if(language.equals("en")){
		        	String titles_en[] = { "Code", "Goods_name"," Tax_denomination","Tax_rate","Specifications","Unit of measurement","Price","Brief_code" };
		        	for(int i = 0; i < titles_en.length; i++){
		        		titles[i] = titles_en[i];
		        	}
		        }else if(language.equals("zh_CN")){
		        	String titles_cn[] = { "代码", "货物名称","税目","税率","规格","计量单位","单价","简码" };
		        	for(int i = 0; i < titles_cn.length; i++){
		        		titles[i] = titles_cn[i];
		        	}
		        }
				Iterator<Cell> titleCells = sheet.getRow(0).cellIterator();
				boolean flag = false;
				while (titleCells.hasNext()) {
					Cell titleCell = (Cell) titleCells.next();
					if (titleCell.getColumnIndex() < nameList.length
							&& nameList[titleCell.getColumnIndex()].length() > 0) {
						String value = titleCell.toString();
						if (!titles[titleCell.getColumnIndex()].equals(value)) {
							flag = true;
							break;
						}
					}
				}
				if (!flag) {
					int sqlResult = 0;
					for (int i = 1; i <= sheet.getLastRowNum(); i++) {
						map = new HashMap<String, String>();
						// 构造插入数据
						Row row = sheet.getRow(i);
						Iterator<Cell> cells = row.cellIterator();
						while (cells.hasNext()) {
							Cell cell = (Cell) cells.next();
							if (cell.getColumnIndex() < nameList.length
									&& nameList[cell.getColumnIndex()].length() > 0) {
								String value = cell.toString();
								if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
									value = String.format("%.0f",
											Double.valueOf(value));
								}
								map.put(nameList[cell.getColumnIndex()], value);
							}
						}
						list.add(map);

						if (list.size() >= 10) {
							param.put("list", list);
							sqlResult = mdataService
									.insertMdataGoodsMappingForImport(param);
							list.clear();
							if (sqlResult != 0) {
								break;
							}
						}
					}

					if (list.size() > 0) {
						param.put("list", list);
						sqlResult = mdataService
								.insertMdataGoodsMappingForImport(param);
					}

					// User user =
					// (User)request.getSession().getAttribute(WebConstants.CURRENT_USER);
					if (sqlResult == 0) {
						result.put("resultcode", 0);
					} else {
						result.put("resultcode", 9);
					}
				} else {
					result.put("resultcode", 99);
				}
			}

			Cookie cookie = new Cookie("JSESSIONID", "");
			cookie.setMaxAge(0);
			httpServletResponse.addCookie(cookie);
		} catch (Exception e) {
			logger.error("{}", e);
		} finally {
			if (result.get("resultcode") == null) {
				result.put("resultcode", 9);
			}
			String returnMsg = JackJson.fromObjectToJson(result);
			writer.print(returnMsg);
			writer.flush();
			writer.close();
		}
	}

	@RequestMapping(value = "/MdataAccountMapping/upload", method = RequestMethod.POST)
	@ResponseBody
	public void MdataAccountupload(
			@RequestParam(value = "upload_file", required = true) MultipartFile multipartFile,
			HttpSession session, HttpServletResponse httpServletResponse,
			HttpServletRequest request, PrintWriter writer) {
		// 获得语言环境
		String language = (String) session.getAttribute(WebConstants.CURRENT_LANG);
		String fileName = multipartFile.getOriginalFilename();
		String fileType = FilenameUtils.getExtension(fileName).toLowerCase();
		HashMap<String, Object> result = new HashMap<String, Object>();
		String company_name = (String) request.getParameter("company_name") == null ? null
				: (String) request.getParameter("company_name");
		String user_name = (String) request.getParameter("user_name") == null ? null
				: (String) request.getParameter("user_name");
		Workbook wb = null;
		Random random = new Random();
		try {
			if (!(fileType.equals("xls") || fileType.equals("xlsx"))) {
				result.put("resultcode", 1);
			} else {
				if (fileType.equals("xls")) {// 如果导入的是excel2003
					wb = new HSSFWorkbook(multipartFile.getInputStream());
				} else if (fileType.equals("xlsx")) {// 如果导入的是excel2007
					wb = new XSSFWorkbook(multipartFile.getInputStream());
				}
				Sheet sheet = wb.getSheetAt(0);
				logger.debug("sheet name = " + wb.getSheetName(0));
				List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
				HashMap<String, Object> map = new HashMap<String, Object>();
				HashMap<String, Object> param = new HashMap<String, Object>();
				param.put("create_time", Tools.getCurrentDay()); // 插入到数据库UPDATE_TIME时，因为UPDATE_TIME字段长度不够，因此字符串被截取
				param.put("account", user_name);
				param.put("company", company_name);

				HashMap<String, Object> check = userService.getUserId(param);
				param.put("user_id", check.get("USER_HID"));

				HashMap<String, Object> company = userService
						.getCompanyId(param);
				int groupId = utilMapper
						.getGroupIdFromCompanyId((Integer) company.get("id"));
				param.put("group_id", groupId);
				param.put("companyId",
						session.getAttribute(WebConstants.CURRENT_COMPANY)
								.toString());
				// 更新update_id,update_name信息 by guxiaoli
				User user = (User) session
						.getAttribute(WebConstants.CURRENT_USER);
				param.put("update_id", Integer.parseInt(user.getUserId()));
				param.put("update_name", user.getAccount());

				String[] nameList = { "pin_account_code", "pin_account" };
				 String titles[] = new String[2];
			        if(language.equals("en")){
			        	String titles_en[] = { "Seller_bankAccount_code", "Seller_bankAccount" };
			        	for(int i = 0; i < titles_en.length; i++){
			        		titles[i] =  titles_en[i];
			        	}
			        }else if(language.equals("zh_CN")){
			        	String titles_cn[] = { "销方银行账号代码", "销方银行账号" };
			        	for(int i = 0; i < titles_cn.length; i++){
			        		titles[i] =  titles_cn[i];
			        	}
			        }
				Iterator<Cell> titleCells = sheet.getRow(0).cellIterator();
				boolean flag = false;
				while (titleCells.hasNext()) {
					Cell titleCell = (Cell) titleCells.next();
					if (titleCell.getColumnIndex() < nameList.length
							&& nameList[titleCell.getColumnIndex()].length() > 0) {
						String value = titleCell.toString();
						if (!titles[titleCell.getColumnIndex()].equals(value)) {
							flag = true;
							break;
						}
					}
				}
				if (!flag) {
					int sqlResult = 0;
					for (int i = 1; i <= sheet.getLastRowNum(); i++) {
						map = new HashMap<String, Object>();
						// 构造插入数据
						Row row = sheet.getRow(i);
						Iterator<Cell> cells = row.cellIterator();
						while (cells.hasNext()) {
							Cell cell = (Cell) cells.next();
							if (cell.getColumnIndex() < nameList.length
									&& nameList[cell.getColumnIndex()].length() > 0) {
								String value = cell.toString();
								if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
									value = String.format("%.0f",
											Double.valueOf(value));
								}
								map.put(nameList[cell.getColumnIndex()], value);
							}
						}
						map.put("mdatasellerid", random.nextInt(9999999));
						map.put("mdatabankname", "chinabank");
						list.add(map);

						if (list.size() >= 10) {
							param.put("list", list);
							sqlResult = mdataService
									.insertMdataAccountMappingForImport(param);
							list.clear();
							if (sqlResult != 0) {
								break;
							}
						}
					}

					if (list.size() > 0) {
						param.put("list", list);
						sqlResult = mdataService
								.insertMdataAccountMappingForImport(param);
					}

					// User user =
					// (User)request.getSession().getAttribute(WebConstants.CURRENT_USER);
					if (sqlResult == 0) {
						result.put("resultcode", 0);
					} else {
						result.put("resultcode", 9);
					}
				} else {
					result.put("resultcode", 99);
				}
			}

			Cookie cookie = new Cookie("JSESSIONID", "");
			cookie.setMaxAge(0);
			httpServletResponse.addCookie(cookie);
		} catch (Exception e) {
			logger.error("{}", e);
		} finally {
			if (result.get("resultcode") == null) {
				result.put("resultcode", 9);
			}
			String returnMsg = JackJson.fromObjectToJson(result);
			writer.print(returnMsg);
			writer.flush();
			writer.close();
		}
	}

	@SuppressWarnings("null")
	@RequestMapping(value = "/MdataClientMapping/{Address}/{Client}/download", method = RequestMethod.GET)
	public ModelAndView MdataClientMappingdownload(
			@PathVariable String Address, @PathVariable String Client,
			HttpSession session, HttpServletRequest request,
			HttpServletResponse response) {

		int id = (Integer) session.getAttribute(WebConstants.CURRENT_COMPANY);
		Object currentCompany = session
				.getAttribute(WebConstants.CURRENT_COMPANY);
		int group_id = utilMapper.getGroupIdFromCompanyId(Integer
				.parseInt(currentCompany.toString()));
		// 获得语言环境
		String language = (String) session.getAttribute(WebConstants.CURRENT_LANG);
		HashMap<String, Object> param = new HashMap<String, Object>();
		HashMap<String, Object> param2 = new HashMap<String, Object>();
		HashMap<String, Object> param3 = new HashMap<String, Object>();
		param.put("id", id);
		param.put("group_id", group_id);
		param2.put("Address", Address.equals("NONE") ? null : Address);
		param2.put("id", id);
		param2.put("Client", Client.equals("ALL") ? null : Client);
		param2.put("language", language);
		param3.put("language", language);
		// dummy data
		ArrayList<HashMap<String, Object>> revenueData = null;
		ArrayList<HashMap<String, Object>> temp = null; // 如果错误就用数组
		HashMap<String, Object> LENGTH = new HashMap<String, Object>();
		temp = mdataService.getMdataClientAddressMappingForImport(param2);
		revenueData = mdataService.getMdataClientMappingForImport(param2);
		for (int i = 0; i < temp.size(); i++) {
			for (int j = 0; j < revenueData.size(); j++) {
				if (temp.get(i).get("c_id")
						.equals(revenueData.get(j).get("c_id"))) {
					Object TEMP = revenueData.get(j).get("code");
					temp.get(i).put("main_name", TEMP);
					break;
				}
			}
		}

		// if(j > revenueData.size()){break;}
		// if(temp.get(i).get("c_id") != revenueData.get(j).get("c_id")){
		// j++;i--; //自动化查询，当联系表的CUST_ID !=
		// 基本表的CUST_ID时，基本表ArrayList的下标进1，联系表下标不动，然后继续判断CUST_ID是否想同，总共比较联系表.size()次
		// continue;
		// }
		// Object TEMP = revenueData.get(j).get("code");
		// temp.get(i).put("main_name", TEMP);
		// j = 0; //此算法目的是为了让联系表获取基本表的CUST_CODE，为了防止联系表.size() >
		// 基本表.size()而发生数组边界错误且为每一个联系表信息查CUST_CODE，
		// //因此将j = 0来让当联系信息为新的信息(旧的已经赋值后之后)重新查询基本表。

		LENGTH.put("clientlength", revenueData.size());
		revenueData.add(0, LENGTH);
		revenueData.add(1, param3);
		revenueData.addAll(temp);
		return new ModelAndView("MdataClientMappingExcelView", "table",
				revenueData);
	}

	@RequestMapping(value = "/MdataGoodsMapping/download", method = RequestMethod.GET)
	public ModelAndView MdataGoodsMappingdownload(HttpSession session,
			HttpServletRequest request) {

		int id = (Integer) session.getAttribute(WebConstants.CURRENT_COMPANY);
		HashMap<String, Object> param1 = new HashMap<String, Object>();
		// 获得语言环境
		String language = (String) session.getAttribute(WebConstants.CURRENT_LANG);
		// dummy data
		ArrayList<HashMap<String, Object>> revenueData = mdataService.getMdataGoodsMappingForImport(id);
		param1.put("language", language);
		param1.put("length", revenueData.size());
		revenueData.add(0,param1);
		return new ModelAndView("MdataGoodsMappingExcelView", "table",
				revenueData);
	}

	@RequestMapping(value = "/MdataAccountMapping/download", method = RequestMethod.GET)
	public ModelAndView MdataAccountMappingdownload(HttpSession session,
			HttpServletRequest request) {

		int id = (Integer) session.getAttribute(WebConstants.CURRENT_COMPANY);
		HashMap<String, Object> param1 = new HashMap<String, Object>();
		// 获得语言环境
		String language = (String) session.getAttribute(WebConstants.CURRENT_LANG);
		// dummy data
		ArrayList<HashMap<String, Object>> revenueData = mdataService
				.getMdataAccountMappingForImport(id);
		param1.put("language", language);
		param1.put("length", revenueData.size());
		revenueData.add(0,param1);
		return new ModelAndView("MdataAccountMappingExcelView", "table",
				revenueData);
	}

	/*
	 * by jason.li 打印回写的导入航天TXT
	 */
	@RequestMapping(value = "/ImportAerMapping/upload", method = RequestMethod.POST)
	@ResponseBody
	public void ImportAerupload(
			@RequestParam(value = "upload_file", required = true) MultipartFile multipartFile,
			HttpSession session, HttpServletResponse httpServletResponse,
			HttpServletRequest request, PrintWriter writer) {

		String fileName = multipartFile.getOriginalFilename();
		String fileType = FilenameUtils.getExtension(fileName).toLowerCase();
		String DOC = "";
		HashMap<String, Object> result = new HashMap<String, Object>();
		BufferedReader reader = null;
		try {
			if (!fileType.equals("txt")) {
				result.put("resultcode", 1);
			} else {
				InputStream bin = multipartFile.getInputStream();
				int p = (bin.read() << 8) + bin.read();
				String code = null;
				switch (p) {
				case 0xefbb:
					code = "UTF-8";
					break;
				case 0xfffe:
					code = "Unicode";
					break;
				case 0xfeff:
					code = "UTF-16BE";
					break;
				case 0x5c75:
					code = "ANSI|ASCII";
					break;
				default:
					code = "GBK";
				}
				bin.close();
				Object currentCompany = session
						.getAttribute(WebConstants.CURRENT_COMPANY);
				reader = new BufferedReader(new InputStreamReader(
						multipartFile.getInputStream(), code));
				String tempString = null;
				List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
				List<HashMap<String, String>> list2 = new ArrayList<HashMap<String, String>>();
				HashMap<String, String> map;
				HashMap<String, Object> param = new HashMap<String, Object>();
				param.put("companyId",
						Integer.parseInt(currentCompany.toString()));
				// 更新update_id,update_name信息 by guxiaoli
				User user = (User) session
						.getAttribute(WebConstants.CURRENT_USER);
				param.put("update_id", Integer.parseInt(user.getUserId()));
				param.put("update_name", user.getAccount());

				// int flag = 0;//标志位
				String doc = "";
				while ((tempString = reader.readLine()) != null) {

					String[] d = tempString.split("~~");
					map = new HashMap<String, String>();

					// 每10条处理一次
					if (list.size() >= 10 && d.length > 12 && d.length < 25) {
						param.put("list", list);
						param.put("list_detail", list2);
						int ret;
						if (!list.isEmpty()) {
							ret = dealService
									.updateInvoiceStateFromImport(param);
							list.clear();
							list2.clear();
						} else {
							ret = -1;
						}
						if (ret == 0) {
							result.put("resultcode", 0);
						} else {
							result.put("resultcode", 9);
							break;
						}
					}

					if (d.length > 12 && d.length < 25) {
						// 主表信息
						int length = d.length;
						if (length != 24) {
							String dest[] = new String[24];
							System.arraycopy(d, 0, dest, 0, d.length);
							d = dest;
						}
						DOC = d[8];
						// if(DOC.isEmpty()){
						// flag = 1;
						// continue;
						// }
						map.put("VOID_FLAG", d[0]);// 作废标志
						map.put("QINGDAN_ERROR", d[1]);// 清单标志
						String type = d[2];
						if (type.equals("0")) {
							type = "VAT";// 增值税专用发票
						} else if (type.equals("1") || type.equals("2")) {
							type = "N_VAT";// 增值税普通发票
						}
						map.put("DOC_TYPE_CODE", type);// 发票种类
						map.put("INVOICE_CODE", d[3]);// 发票代码
						map.put("INVOICE_NO", d[4]);// 发票号码
						map.put("TOTAL_LINE_NO", d[5]);// 商品明细行数
						map.put("INVOICE_DATE", d[6]);// 开票日期
						map.put("DOCUMENT_DATE", d[7]);// 税务月份
						if (d[8].isEmpty()) {
							if (d[3].length() >= 2) {
								DOC = d[3].substring(d[3].length() - 2) + d[4];
							}

						}
						map.put("DOCUMENT_NO", DOC);// 销售单据号

						if (!d[9]
								.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$")) {// 不是数字
							list.clear();
							list2.clear();

							doc = DOC;
							break;
						}
						if (!d[10]
								.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$")) {// 不是数字
							list.clear();
							list2.clear();
							doc = DOC;
							break;
						}
						if (!d[11]
								.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$")) {// 不是数字
							list.clear();
							list2.clear();
							doc = DOC;
							break;
						}

						map.put("AMOUNT_CUR_TOTAL", d[9].length() < 1 ? "0.00"
								: d[9]);// 不含税金额
						map.put("TAX_RATE", d[10].length() < 1 ? "0.00" : d[10]);// 税率
						map.put("TAX_AMOUNT", d[11].length() < 1 ? "0.00"
								: d[11]);// 税额
						map.put("CUST_NAME_CHN", d[12]);// 购方名称
						map.put("TAX_CODE", d[13]);// 购方税号
						map.put("ADDRESS", d[14]);// 购方地址电话
						map.put("BANK_ACCOUNT", d[15]);// 购方银行账号
						map.put("SELLER_NAME", d[16]);// 销方名称
						map.put("SELLER_TAX_CODE", d[17]);// 销方税号
						map.put("SELLER_ADDRESS", d[18]);// 销方地址电话
						map.put("SELLER_BANK_ACCOUNT", d[19]);// 销方银行账号
						map.put("REMARK", d[20]);// 备注
						// map.put("OPERATOR", d[21].equals("开票员") ? "" :
						// d[21]);//开票员
						// map.put("CHECKER", d[22].equals("复核人") ? "" :
						// d[22]);//复核人
						// map.put("REMITTEE", d[23].equals("收款人") ? "" :
						// d[23]);//收款人
						map.put("OPERATOR", d[21]);// 开票员
						map.put("CHECKER", d[22]);// 复核人
						if (d.length == 24) {
							map.put("REMITTEE", d[23]);// 收款人
						} else {
							map.put("REMITTEE", "");// 收款人
						}
						list.add(map);
						// 以上为发票项
					} else if (d.length > 4 && d.length < 12) {
						// 以下为明细项
						int length = d.length;
						if (length != 11) {
							String dest[] = new String[11];
							System.arraycopy(d, 0, dest, 0, d.length);
							d = dest;
						}

						// if(DOC.isEmpty()){
						// continue;
						// }
						map.put("ZHEKOUHANG_ERROR", d[0]);// 折扣行标志
						map.put("GOODS_NAME", d[1]);// 货物名称
						map.put("SPEC", d[2]);// 规格型号
						map.put("GOODS_UNIT", d[3]);// 计量单位
						if (!d[4]
								.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$")) {// 不是数字
							list.clear();
							list2.clear();
							doc = DOC;
							break;
						}
						if (!d[5]
								.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$")) {// 不是数字
							list.clear();
							list2.clear();
							doc = DOC;
							break;
						}
						if (!d[6]
								.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$")) {// 不是数字
							list.clear();
							list2.clear();
							doc = DOC;
							break;
						}
						if (!d[7]
								.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$")) {// 不是数字
							list.clear();
							list2.clear();
							doc = DOC;
							;
							break;
						}
						if (!d[8]
								.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$")) {// 不是数字
							list.clear();
							list2.clear();
							doc = DOC;
							break;
						}
						if (!d[9]
								.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$")) {// 不是数字
							list.clear();
							list2.clear();
							doc = DOC;
							break;
						}
						map.put("QUANTITY", d[4].length() < 1 ? "0.00" : d[4]);// 数量
						map.put("AMOUNT_CUR", d[5].length() < 1 ? "0.00" : d[5]);// 金额
						map.put("TAX_RATE", d[6].length() < 1 ? "0.00" : d[6]);// 税率
						map.put("TAX_AMOUNT", d[7].length() < 1 ? "0.00" : d[7]);// 税额
						map.put("PRICE_CUR", d[8].length() < 1 ? "0.00" : d[8]);// 单价
						map.put("PRICE_WAY", d[9]);// 单价价格方式
						if (d.length == 11) {
							map.put("TAX_ITEM", d[10]);// 商品税目
						} else {
							map.put("TAX_ITEM", "");// 商品税目
						}

						map.put("DOCUMENT_NO", DOC);// 单据号
						list2.add(map);
					}
				}
				reader.close();
				param.put("list", list);
				param.put("list_detail", list2);
				String companyId = session.getAttribute(
						WebConstants.CURRENT_COMPANY).toString();
				param.put("companyId", companyId);
				int ret;
				if (!list.isEmpty()) {
					ret = dealService.updateInvoiceStateFromImport(param);
				} else {
					ret = -1;
				}
				if (ret == 0) {
					result.put("resultcode", 0);
					// result.put("flag",flag);
				} else {
					result.put("resultcode", doc);
				}
			}
			String returnMsg = JackJson.fromObjectToJson(result);
			writer.print(returnMsg);
			Cookie cookie = new Cookie("JSESSIONID", "");
			cookie.setMaxAge(0);
			httpServletResponse.addCookie(cookie);
		} catch (Exception e) {
			logger.error("{}", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}

	/**
	 * 
	 * 数据信息错误信息提示
	 * 
	 * @param request
	 * @param response
	 * @return
	 */
	@RequestMapping(value = "/search/errordetail/download/{import_code}/download", method = RequestMethod.GET)
	public ModelAndView searchErrorDetail(@PathVariable String import_code,
			HttpServletRequest request, HttpServletResponse response) {
		HashMap<String, Object> param = new HashMap<String, Object>();
		if (!Tools.isEmpty(import_code)) {
			param.put("import_code", import_code);
		}
		ArrayList<HashMap<String, Object>> errorDetailList = tcodeService
				.reportErrorDetail(param);
		return new ModelAndView("ErrorDetailExcelView", "table",
				errorDetailList);
	}

	// 打印回写中的导入excel功能
	@RequestMapping(value = "ImportExcelAerMapping/upload", method = RequestMethod.POST)
	@ResponseBody
	public void ImportExcelupload(
			@RequestParam(value = "upload_file", required = true) MultipartFile multipartFile,
			HttpSession session, HttpServletResponse httpServletResponse,
			HttpServletRequest request, PrintWriter writer) {

		String fileName = multipartFile.getOriginalFilename();
		String fileType = FilenameUtils.getExtension(fileName).toLowerCase();
		HashMap<String, Object> result = new HashMap<String, Object>();
		String company_name = (String) request.getParameter("company_name") == null ? null
				: (String) request.getParameter("company_name");
		String user_name = (String) request.getParameter("user_name") == null ? null
				: (String) request.getParameter("user_name");
		Workbook wb = null;
		try {
			if (!(fileType.equals("xls") || fileType.equals("xlsx"))) {
				result.put("resultcode", 1);
			} else {
				if (fileType.equals("xls")) {// 如果导入的是excel2003
					wb = new HSSFWorkbook(multipartFile.getInputStream());
				} else if (fileType.equals("xlsx")) {// 如果导入的是excel2007
					wb = new XSSFWorkbook(multipartFile.getInputStream());
				}
				Sheet sheet = wb.getSheetAt(0);
				logger.debug("sheet name = " + wb.getSheetName(0));
				List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
				HashMap<String, Object> map = new HashMap<String, Object>();
				HashMap<String, Object> param = new HashMap<String, Object>();
				param.put("create_time", Tools.getCurrentDay()); // 插入到数据库UPDATE_TIME时，因为UPDATE_TIME字段长度不够，因此字符串被截取
				param.put("account", user_name);
				param.put("company", company_name);
				param.put("new_doc_status", 1);
				param.put("company_Id", (Integer) session
						.getAttribute(WebConstants.CURRENT_COMPANY));

				HashMap<String, Object> check = userService.getUserId(param);
				param.put("user_id", check.get("USER_HID"));

				HashMap<String, Object> company = userService
						.getCompanyId(param);
				int groupId = utilMapper
						.getGroupIdFromCompanyId((Integer) company.get("id"));
				param.put("group_id", groupId);
				// 更新update_id,update_name信息 by guxiaoli
				User user = (User) session
						.getAttribute(WebConstants.CURRENT_USER);
				param.put("update_id", Integer.parseInt(user.getUserId()));
				param.put("update_name", user.getAccount());

				// String []nameList = { //基本信息
				// "DOCUMENT_NO",
				// "DOCUMENT_DATE",
				// "BANK_ACCOUNT",
				// "COMPANY_ID",
				// "TAX_CODE",
				// "CUST_CODE",
				// "DOC_STATUS",
				// "CUST_NAME_CHN"
				// };
				// String titles[] = { "DOCUMENT_NO",
				// "DOCUMENT_DATE","BANK_ACCOUNT","COMPANY_ID","TAX_CODE" //基本信息
				// ,"CUST_CODE","DOC_STATUS","CUST_NAME_CHN" }; //基本信息
				String[] nameList_All = { "VOID_FLAG", "DOC_TYPE_CODE",
						"DOCUMENT_NO", "INVOICE_CODE", "INVOICE_NO",
						"CUST_CODE", "CUST_NAME_CHN", "TAX_CODE", "ADDRESS",
						"BANK_ACCOUNT", "INVOICE_DATE", "REMARK",
						"OPERATOR",
						"REMITTEE",
						"CHECKER",
						"SELLER_BANK_ACCOUNT",
						"SELLER_ADDRESS",
						// 以上为TAX_D_ID_HEAD表内容
						// 以下为TAX_D_ID_DETAIL表内容
						"GOODS_CODE", "GOODS_NAME", "SPEC", "GOODS_UNIT",
						"QUANTITY", "AMOUNT_CUR", "TAX_RATE", "TAX_AMOUNT",
						"DISCOUNT_SUM", "DISCOUNT_TAX" };

				String[] titles_All = { "作废标志", "发票种类", "单据号", "发票代码", "发票号码",
						"客户编号", "客户名称", "客户税号", "客户地址电话", "客户银行及帐号", "开票日期",
						"备注", "开票人", "收款人", "复核人", "销方银行及帐号",
						"销方地址电话",
						// 以上为TAX_D_ID_HEAD表内容
						// 以下为TAX_D_ID_DETAIL表内容
						"商品编号", "商品名称", "规格型号", "计量单位", "数量", "金额（含税）", "税率",
						"税额", "折扣金额(含税)", "折扣税额" };
				Iterator<Cell> titleCells = sheet.getRow(0).cellIterator();
				boolean flag = false;
				while (titleCells.hasNext()) {
					Cell titleCell = (Cell) titleCells.next();
					if (titleCell.getColumnIndex() < nameList_All.length
							&& nameList_All[titleCell.getColumnIndex()]
									.length() > 0) {
						String value = titleCell.toString();
						if (!titles_All[titleCell.getColumnIndex()]
								.equals(value)) {
							flag = true;
							break;
						}
					}
				}
				if (!flag) {
					int sqlResult = 0;
					for (int i = 1; i <= sheet.getLastRowNum(); i++) {
						map = new HashMap<String, Object>();
						// int Writeable = 0;
						// 构造插入数据
						Row row = sheet.getRow(i);
						Iterator<Cell> cells = row.cellIterator();
						while (cells.hasNext()) {
							Cell cell = (Cell) cells.next();
							if (cell.getColumnIndex() < nameList_All.length
									&& nameList_All[cell.getColumnIndex()]
											.length() > 0) {
								String value = cell.toString();
								if (nameList_All[cell.getColumnIndex()]
										.equals("INVOICE_DATE")) {
									System.out
											.println("nameList_All[cell.getColumnIndex()]: "
													+ nameList_All[cell
															.getColumnIndex()]);
									double d = cell.getNumericCellValue();
									Date date = HSSFDateUtil.getJavaDate(d);
									SimpleDateFormat sdf = new SimpleDateFormat(
											"yyyy-MM-dd HH:mm:ss.SSS");
									value = sdf.format(date);
									map.put(nameList_All[cell.getColumnIndex()],
											value);
									continue;
								} else if (nameList_All[cell.getColumnIndex()]
										.equals("TAX_RATE")
										|| nameList_All[cell.getColumnIndex()]
												.equals("TAX_AMOUNT")
										|| nameList_All[cell.getColumnIndex()]
												.equals("QUANTITY")
										|| nameList_All[cell.getColumnIndex()]
												.equals("AMOUNT_CUR")
										|| nameList_All[cell.getColumnIndex()]
												.equals("DISCOUNT_SUM")
										|| nameList_All[cell.getColumnIndex()]
												.equals("DISCOUNT_TAX")) {
									if (!value
											.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$")) {// 判断如果不是数字就把他置为0.00
										double f_value = 0.00;
										map.put(nameList_All[cell
												.getColumnIndex()], f_value);
										continue;
									}
									if (value.equals("") || value == null) {
										double f_value = 0.00;
										map.put(nameList_All[cell
												.getColumnIndex()], f_value);
										continue;
									}
									float f_value = Float.parseFloat(value);
									map.put(nameList_All[cell.getColumnIndex()],
											f_value);
									continue;
								} else if ((cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
									if (value.equals("") || value == null) {
										continue;
									}
									value = String.format("%.0f",
											Double.valueOf(value));
									System.out
											.println("nameList_All[cell.getColumnIndex()]: "
													+ nameList_All[cell
															.getColumnIndex()]);
									map.put(nameList_All[cell.getColumnIndex()],
											value);
								} else {
									if (value.equals("增票")) {
										value = "VAT";
									} else if (value.equals("普票")) {
										value = "N_VAT";
									} else if (value.equals("是")) {
										value = "1";
									} else if (value.equals("否")) {
										value = "0";
									}
									map.put(nameList_All[cell.getColumnIndex()],
											value);
								}
							}
						}
						list.add(map);

						if (list.size() >= 10) {
							param.put("list", list);
							sqlResult = dealService
									.ImportExcelAerMapping(param);
							list.clear();
							if (sqlResult != 0) {
								break;
							}
						}
					}

					if (list.size() > 0) {
						param.put("list", list);
						sqlResult = dealService.ImportExcelAerMapping(param);
					}

					// User user =
					// (User)request.getSession().getAttribute(WebConstants.CURRENT_USER);
					if (sqlResult == 0) {
						result.put("resultcode", 0);
					} else {
						result.put("resultcode", 9);
					}
				} else {
					result.put("resultcode", 99);
				}
			}

			Cookie cookie = new Cookie("JSESSIONID", "");
			cookie.setMaxAge(0);
			httpServletResponse.addCookie(cookie);
		} catch (Exception e) {
			logger.error("{}", e);
		} finally {
			if (result.get("resultcode") == null) {
				result.put("resultcode", 9);
			}
			String returnMsg = JackJson.fromObjectToJson(result);
			writer.print(returnMsg);
			writer.flush();
			writer.close();
		}
	}
}
